/**
 * Created by hanj on 2020/11/11 19:52
 */
package com.example.demo;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Excel导出工具类
 *
 * @author caoju
 */
@Slf4j
public class ExcelUtils {

    /**
     * 允许导出的最大条数
     */
    private static final Integer EXPORT_EXCEL_MAX_NUM = 10000;

    /**
     * 导出Excel
     *
     * @param workbook workbook流
     * @param fileName 文件名
     * @param response 响应
     */
    public static void exportExcel(Workbook workbook, String fileName, HttpServletResponse response) {
        //给文件名拼接上日期
        fileName = fileName + System.currentTimeMillis();
        //输出文件
        try (OutputStream out = response.getOutputStream()) {
            //获取文件名并转码
            String name = URLEncoder.encode(fileName, "UTF-8");
            //设置编码
            response.setCharacterEncoding("UTF-8");
            //设置http响应头告诉浏览器，以下载的形式处理响应的数据
            response.setContentType("application/force-download");
            //设置下载文件的默认名称
            response.setHeader("Content-Disposition", "attachment;filename=" + name + ".xlsx");
            //输出表格
            workbook.write(out);
        } catch (IOException e) {
            log.error("文件导出异常,详情如下:", e);
        } finally {
            try {
                if (workbook != null) {
                    //关闭输出流
                    workbook.close();
                }
            } catch (IOException e) {
                log.error("文件导出异常,详情如下:", e);
            }
        }
    }

    /**
     * 获取导出的 Workbook对象
     *
     * @param title     大标题
     * @param sheetName 页签名
     * @param object    导出实体
     * @param list      数据集合
     * @return Workbook
     */
    public static Workbook getWorkbook(String title, String sheetName, Class object, List list) {
        //判断导出数据是否为空
        if (list == null) {
            list = new ArrayList<>();
        }
        //判断导出数据数量是否超过限定值
        if (list.size() > EXPORT_EXCEL_MAX_NUM) {
            title = "导出数据行数超过:" + EXPORT_EXCEL_MAX_NUM + "条,无法导出、请添加查询条件后再进行导出!";
            list = new ArrayList<>();
        }
        //导出参数
        ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
        //设置导出样式，可以不设置使用默认的样式，这里用的是自定义样式
        exportParams.setStyle(ExcelStyle.class);
        //输出Workbook流
        return ExcelExportUtil.exportExcel(exportParams, object, list);
    }

    /**
     * 获取导出的 Workbook对象
     *
     * @param path 模板路径
     * @param map  导出内容map
     * @return Workbook
     */
    public static Workbook getWorkbook(String path, Map<String, Object> map) {
        //获取导出模板
        TemplateExportParams params = new TemplateExportParams(path);
        //设置导出样式
        params.setStyle(ExcelStyle.class);
        //输出Workbook流
        return ExcelExportUtil.exportExcel(params, map);
    }

    /**
     * 利用反射机制遍历对象的属性值，并放入单元格中，为excel添加样式
     */
    public static void fillExcelData(List<User> list,List<Teacher> list2, String[] headers,String[] headers2, String title,
                                     HttpServletResponse response) throws Exception {
        // 创建工作薄
        HSSFWorkbook wb = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = wb.createSheet(title);

        // 设置表格默认宽度为15个字节
        sheet.setDefaultColumnWidth((short) 25);

        //颜色控制器
        Map<Integer,Integer> colorMap = new HashMap<>();
        int colorIndex = 9;

        ExcelStyle excelStyle = new ExcelStyle(wb);
        CellStyle style = excelStyle.getTitleStyle(wb);//获取标题样式
        CellStyle style2 = excelStyle.getHeaderStyle(wb);//获取表头样式
        CellStyle style3 = excelStyle.getHeaderExplainStyle(wb);//获取表头说明样式
//        CellStyle style4 = excelStyle.getListContentStyle(wb);//获取列表内容样式

        // 标题
        int rowIndex = 0;//行数索引
        // 合并单元格0到headers.length -1行
        sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, headers.length - 1));
        HSSFRow row0 = sheet.createRow(rowIndex++);
        row0.setHeightInPoints(26);
        HSSFCell cell1 = row0.createCell(0);
        cell1.setCellStyle(style);
        cell1.setCellValue(title);
        for (short i = 1; i < headers.length; i++) {
            HSSFCell cell = row0.createCell(i);
            cell.setCellStyle(style);
        }

        // 合并单元格0到headers.length -2行
        sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, headers.length - 1));
        //添加列表说明
        HSSFRow row1 = sheet.createRow(rowIndex++);
        row1.setHeightInPoints(16);
        HSSFCell cell2 = row1.createCell(0);
        cell2.setCellStyle(style3);
        cell2.setCellValue("1.牵头申报");
        for (short i = 1; i < headers.length; i++) {
            HSSFCell cell = row1.createCell(i);
            cell.setCellStyle(style3);
        }

        // 产生表格表头行
        HSSFRow row = sheet.createRow(rowIndex++);
        row.setHeightInPoints(20);
        for (short i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(style2);
            cell.setCellValue(headers[i]);
        }
        // 产生表格内容行
        for (User obj : list) {
            // 利用反射得到对象的属性
            Class cls = obj.getClass();
            java.lang.reflect.Field[] flds = cls.getDeclaredFields(); // 得到私有属性
            row = sheet.createRow(rowIndex++);
            row.setHeightInPoints(30);//设置行高
            CellStyle style4 = excelStyle.getListContentStyle(wb);//获取列表内容样式
            if (colorMap.get(obj.getId()) != null) {
                style4.setFillForegroundColor(IndexedColors.fromInt(colorMap.get(obj.getId())).index);
                style4.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            } else {
                style4.setFillForegroundColor(IndexedColors.fromInt(colorIndex).index);
                style4.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                colorMap.put(obj.getId(), colorIndex);//加入颜色控制器
                colorIndex++;
                if (colorIndex > 64) {//调色板最大索引64
                    colorIndex = 9;
                }
            }
            for (int j = 0; j < headers.length; j++) {
                flds[j].setAccessible(true);
                HSSFCell cell = row.createCell(j);
                cell.setCellStyle(style4);
                cell.setCellValue((flds[j].get(obj)).toString());
            }
        }


        sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, headers2.length - 1));
        //添加列表说明
        HSSFRow row2 = sheet.createRow(rowIndex++);
        row2.setHeightInPoints(16);
        HSSFCell cell3 = row2.createCell(0);
        cell3.setCellStyle(style3);
        cell3.setCellValue("1.参与申报");
        for (short i = 1; i < headers2.length; i++) {
            HSSFCell cell = row2.createCell(i);
            cell.setCellStyle(style3);
        }

        // 产生表头行
        HSSFRow row_cy = sheet.createRow(rowIndex++);
        row_cy.setHeightInPoints(20);
        for (short i = 0; i < headers2.length; i++) {
            HSSFCell cell = row_cy.createCell(i);
            cell.setCellStyle(style2);
            cell.setCellValue(headers2[i]);
        }

        // 产生表格内容行
        for (Teacher obj : list2) {
            // 利用反射得到对象的属性
            Class cls = obj.getClass();
            java.lang.reflect.Field[] flds = cls.getDeclaredFields(); // 得到私有属性
            row = sheet.createRow(rowIndex++);
            row.setHeightInPoints(30);//设置行高
            CellStyle style4 = excelStyle.getListContentStyle(wb);//获取列表内容样式
            if (colorMap.get(obj.getId()) != null) {
                style4.setFillForegroundColor(IndexedColors.fromInt(colorMap.get(obj.getId())).index);
                style4.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            } else {
                style4.setFillForegroundColor(IndexedColors.fromInt(colorIndex).index);
                style4.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                colorMap.put(obj.getId(), colorIndex);//加入颜色控制器
                colorIndex++;
                if (colorIndex > 64) {//调色板最大索引64
                    colorIndex = 9;
                }
            }
            for (int j = 0; j < headers2.length; j++) {
                flds[j].setAccessible(true);
                HSSFCell cell = row.createCell(j);
                cell.setCellStyle(style4);
                cell.setCellValue((flds[j].get(obj)).toString());
            }
        }


        String fileName = title + ".xls";
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("utf-8"), "iso8859-1"));
        response.setContentType("application/ynd.ms-excel;charset=UTF-8");
        OutputStream out = response.getOutputStream();
        wb.write(out);
        out.flush();
        out.close();
    }

    /**
     * 列表数据格式
     *
     * @param workbook
     * @return
     * @author 邓成波
     * @date 创建时间 2017年3月21日 下午4:32:13
     */
    public static HSSFCellStyle getStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体名字
        font.setFontName("宋体"); //字体
        //设置字体大小
        font.setFontHeightInPoints((short) 11);
        //字体加粗
        //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(true);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;
    }

    /**
     * 列表表头用   数据格式表头
     *
     * @param workbook
     * @return
     * @author 邓成波
     * @date 创建时间 2017年3月21日下午4:32:13
     */
    public static HSSFCellStyle getStyleTitle(HSSFWorkbook workbook) {

        //设置字体
        HSSFFont font = workbook.createFont();
        //设置字体名字
        font.setFontName("宋体"); //字体
        //设置字体大小
        font.setFontHeightInPoints((short) 10);
        //字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();

        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setFillForegroundColor(IndexedColors.RED.getIndex());
        style.setFillBackgroundColor(IndexedColors.RED.getIndex());

        //设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;
    }

    /**
     * 设定自定义字体颜色信息
     *
     * @param str   颜色
     * @param index 编号
     * @param wb    execl对象
     * @author 邓成波
     * @date 创建时间 2017年3月21日 下午12:01:09
     */
    public void setHSSFPalette(String str, int index, HSSFWorkbook wb) {
        //处理把它转换成十六进制并放入一个数
        int[] color = new int[3];
        color[0] = Integer.parseInt(str.substring(1, 3), 16);
        color[1] = Integer.parseInt(str.substring(3, 5), 16);
        color[2] = Integer.parseInt(str.substring(5, 7), 16);
        //自定义颜色
        HSSFPalette palette = wb.getCustomPalette();
        //设置自定义颜色的下标，接下来会用到
        palette.setColorAtIndex(((short) index), (byte) color[0], (byte) color[1], (byte) color[2]);
    }


}
